from openpyxl import load_workbook
import time
import pandas as pd


def time_function(func):
    def inner_function(*args, **kwargs):
        start_time = time.time()
        result = func(*args, **kwargs)
        end_time = time.time()
        print(f"[{func.__name__}] civil time:", end_time - start_time)
        return result

    return inner_function


def get_max_row(sheet):
    i = sheet.max_row
    real_max_row = 0
    while i > 0:
        row_dict = {i.value for i in sheet[i]}
        if row_dict == {None}:
            i = i - 1
        else:
            real_max_row = i
            break

    return real_max_row


@time_function
def write_excel(li, jgpath, sheet_name):
    df_li = pd.DataFrame(li, columns=['sj', 'dh', 'mb', 'gs', 'jg', 'yy', 'aa', 'bb'])
    # 加载Excel文件
    book = load_workbook(filename=jgpath)
    sheet = book[sheet_name]
    end_row = get_max_row(sheet)

    # 将数据写入Excel中
    writer = pd.ExcelWriter(jgpath, engine='openpyxl')
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    df_li.to_excel(writer, sheet_name=sheet_name, startrow=end_row, startcol=0, index=False,
                   header=False)  # 将数据写入excel中的aa表,从第一个空行开始写
    writer.save()  # 保存
    writer.close()


if __name__ == '__main__':
    file_name = './审核结果.xlsx'
    title = ['sj', 'dh', 'mb', 'gs', 'jg', 'yy', 'aa', 'bb']
    new_data = {i: title for i in title}
    write_excel(new_data, file_name, 'Sheet1')
